ALTER PROCEDURE [dbo].[Proc_S1_GetDataFromUCIS_A00014]
AS
/****************************************************
  Program_Name: spe_grab_ucis_sfmessageinfo
  Program_Desc: 水务预通知抓取程序-1
  Commentary:上海过河兵
  Revision: V1.0
  Author: Tao
  Date: 2019-09-01
***************************************************/
Begin
	Begin Try
  
	Declare @Plancount Int
	Declare @Datacount Int
	Declare @Totalcount Int
	Declare @Phonecount Int
	Declare @Rec_Guid Varchar(36)
	Declare @Deptcode Varchar(10)
	declare @SENDYEARMONTH INT
	declare @BATCH INT
	declare @MESSAGETYPENAME VARCHAR(50)
	declare @MESSAGESTATUS INT
	declare @createtime DATETIME
	declare @MESSAGETYPECODE VARCHAR(50)
	declare @MESSAGECOUNT VARCHAR(50)
	declare @SMS_NAME VARCHAR(50)
	declare @MESSAGEINFOID VARCHAR(50)
	DECLARE @tguid VARCHAR(36)
	DECLARE @sname VARCHAR(255)
    declare @te_Starttime DATETIME

    Set @te_Starttime = GetDate()
    --检查是否有待抓取的任务计划
    Select @Plancount = Count(*)
      From Openquery(Ucis115, 'Select * From Ucis.Messageinfo')
     Where Messagetypecode = 'A00014'
       And Messagestatus = '1'
       And Sendyearmonth = Replace(Convert(Varchar(7), Getdate(), 120), '-', '')
       And Createtime > Convert(Varchar(100), Getdate(), 23)
     
	If @Plancount>0
	Begin
        Select Top 1 @Messageinfoid = Messageinfoid,
               @Sendyearmonth = Sendyearmonth, --年月
               @Batch = Batch, --批次
               @Messagetypename = Messagetypename, --类型（名称）
               @Deptcode = Deptcode, --部门编号
               @Messagecount = Messagecount, --短信总数
               @Messagestatus = Messagestatus, --短信状态
               @Createtime = Createtime, --时间
               @Messagetypecode = Messagetypecode --类型(编号)
          From Openquery(Ucis115, 'SELECT * FROM ucis.MessageInfo')
         Where Messagetypecode = 'A00014'
           And Messagestatus = '1'
           And Sendyearmonth = Replace(Convert(Varchar(7), Getdate(), 120), '-', '')
         Order By Createtime Desc

        Select @Plancount = Count(*)
          From Ucis_Smssendplan
         Where Type_Id = @Messagetypecode
           And Batch = @Batch
           And Deptcode = @Deptcode
           And Replace(Convert(Varchar(7), Cteateddate, 120), '-', '') = @Sendyearmonth
           And Messageinfoid = @Messageinfoid

		if @plancount<1
            BEGIN
                --小于1则说明本地计划表没有相匹配的计划任务，这时则需创建计划任务
                Insert Into Ucis_Smssendplan
                  (Rec_Guid, Type_Id, Batch, Cteateddate, Deptcode, Sms_Name, Status, Jhkstime, Messageinfoid, Sms_Jssc, Messagecount)
                  Select Top 1 Newid(),
                         Messagetypecode,
                         Batch,
                         Getdate(),
                         M1.Deptcode,
                         Left(Sendyearmonth, 4) + '年' + Substring(Sendyearmonth, 5, 6) + '月' + C1.Department + M1.Messagetypename +
                         '批次(' + Cast(Batch As Varchar) + '-' + Cast(Batchtimes As Varchar) + ')',
                         0,
                         Createtime,
                         Messageinfoid,
                         300,
                         Messagecount
                    From Openquery(Ucis115, 'SELECT * FROM ucis.MessageInfo') M1
                    Left Join Ucis_Deptcode C1
                      On C1.Deptcode = M1.Deptcode
                   Where Messagetypecode = 'A00014'
                     And Messagestatus = '1'
                     And Sendyearmonth = Replace(Convert(Varchar(7), Getdate(), 120), '-', '')
                     And Messageinfoid = @Messageinfoid
                   Order By Createtime Desc

		        declare plan_A00014 cursor For Select Rec_Guid, Deptcode From Ucis_Smssendplan Where Type_Id = 'A00014' And Status = 0 ORDER BY cteateddate desc
    
		        open plan_A00014 fetch next from plan_A00014 into @rec_guid,@DEPTCODE
		        while @@fetch_status=0
		        begin
		            --检查UCIS系统是否有需要抓取的任务
                    Select @Datacount = Count(*)
                      From Openquery(Ucis115, 'Select * From Ucis.Ucis_Sf_Message_Info Where Ghbtype Is Null')
                     Where Yf = Month(Getdate())
                       And Nf = Year(Getdate())
                       And Deptcode = @Deptcode
                       And Batch = @Batch

                    if @datacount>0
			            Begin
                            --将目前开始处理的计划插入监控表
                            Insert Into Ucis_Sms_Monitoring
                              (Sms_Guid, Source_Sum, Type_Id, Grab_Starttime, Createdate)
                            Values
                              (@Rec_Guid, @Datacount, 'A00014', Getdate(), Getdate())

                            --将目前开始处理的计划预抓取结果插入表中
                            Insert Into Ucis_Cj_Result
                              (Cj_Guid, Type, Sourcedata, Createdate)
                            Values
                              (@Rec_Guid, 'A00014', @Datacount, Getdate())

                            Begin
                                --将UCIS数据批量抓取到CRM
                                --调整了dblink查询语句sql方式，提高查询性能跟效率 Yf = Month(Getdate()) And Nf = Year(Getdate())
                                Insert Into Ucis_Sf_Message_Info
                                  (Nf,
                                   Yf,
                                   Sjhm,
                                   Yhbh,
                                   Ysdz,
                                   Lcbrq,
                                   Cbrq,
                                   Ysl,
                                   Bysf,
                                   Bypsf,
                                   Byljf,
                                   Byje,
                                   Tqje,
                                   Bhgs,
                                   Fslx,
                                   Gss,
                                   Result,
                                   Sendtime,
                                   Id,
                                   Sfznj,
                                   Psfznj,
                                   Ljfznj,
                                   Itemid,
                                   Deptcode,
                                   Sms_Guid,
                                   Smsmix,
                                   Readingstatus,
                                   Reducedmoney,
                                   Isgsjt,
                                   Issbyh)
                                  Select Nf,
                                         Yf,
                                         Sjhm,
                                         Yhbh,
                                         Ysdz,
                                         Lcbrq,
                                         Cbrq,
                                         Ysl,
                                         Bysf,
                                         Bypsf,
                                         Byljf,
                                         Byje,
                                         Tqje + Sfznj + Psfznj + Ljfznj,
                                         Bhgs,
                                         Fslx,
                                         Gss,
                                         Result,
                                         Sendtime,
                                         Id,
                                         Sfznj,
                                         Psfznj,
                                         Ljfznj,
                                         Itemid,
                                         Deptcode,
                                         @Rec_Guid,
                                         Smsmix,
                                         Readingstatus,
                                         Case
                                           When Reducedmoney Is Null Then
                                            0
                                           Else
                                            Reducedmoney
                                         End,
                                         0,
                                         Issbyh
                                    From Openquery(Ucis115, 'Select * From Ucis.Ucis_Sf_Message_Info Where Ghbtype Is Null And Yf = To_Char(Sysdate, ''mm'') And Nf = To_Char(Sysdate, ''yyyy'')')
                                   Where 1 = 1
                                     And Deptcode = @Deptcode
                                     And Batch = @Batch

                                --更改状态
                                --调整了dblink查询语句sql方式，提高查询性能跟效率 Yf = Month(Getdate()) And Nf = Year(Getdate())
                                Update Openquery(Ucis115, 'SELECT * FROM ucis.Ucis_Sf_Message_Info where ghbtype is null and yf=to_char(sysdate,''mm'') and nf=to_char(sysdate,''yyyy'')')
                                   Set Ghbtype = 1
                                 Where 1 = 1
                                   And Deptcode = @Deptcode
                                   And Batch = @Batch

                                --计数
                                Select @Phonecount = Count(*)
                                  From Ucis_Sf_Message_Info
                                 Where Sms_Guid = @Rec_Guid
                                   And Len(Sjhm) = 11;

                                Update Ucis_Cj_Result Set Phone_Count = @Phonecount Where Cj_Guid = @Rec_Guid;

                                Select @Totalcount = Count(*) From Ucis_Sf_Message_Info Where Sms_Guid = @Rec_Guid;

                                Update Ucis_Sms_Monitoring
                                   Set Grab_Sum = @Totalcount, Grab_Endtime = Getdate(), Grab_Status = 1
                                 Where Sms_Guid = @Rec_Guid;

                                Insert Into Ucis_Sms_Grab Values (@Rec_Guid, 'A00014', Null, Null);

                            End
        
			            End
      
			        fetch next from plan_A00014 into @rec_guid,@DEPTCODE
      
		        End
    
		    close plan_A00014
		    deallocate plan_A00014
            END

			BEGIN
                Update Top(1) Openquery(Ucis115, 'SELECT * FROM ucis.MessageInfo')
                   Set Messagestatus = '2', Modifytime = Getdate()
                 Where Sendyearmonth = @Sendyearmonth
                   And Batch = @Batch
                   And Messagetypename = @Messagetypename
                   And Deptcode = @Deptcode
                   And Messagecount = @Messagecount
                   And Messagestatus = @Messagestatus
                   And Createtime = @Createtime
                   And Messagetypecode = @Messagetypecode
                   And Messageinfoid = @Messageinfoid
			END

            BEGIN
                Select Top 1 @Tguid = Rec_Guid, @Sname = Sms_Name
                  From Ucis_Smssen
                 Where Messageinfoid = @Messageinfoid
                 Order By Cteateddate Descdplan;

                Insert Into Message_Operation_His Values (@Tguid, @Sname, 'job', Getdate(), 'UCIS存在计划，进行计划抓取', Newid());
            END

            BEGIN
                Insert Into Log_Proc_Dealtime
                    (Log_Guid, Proc_Name, Run_No, Starttime, Endtime, Dealinfo, Dealtime, Remark)
                Values
                    (Newid(), 'Proc_S1_GetDataFromUCIS_A00014', @tguid, @te_Starttime, Getdate(), '预通知', DateDiff(MS, @te_Starttime, GetDate()), 'Proc_S1_GetDataFromUCIS_A00014任务调用:水务催缴流程预通知')

            END
	End

	End Try

	Begin Catch

		Exec Proc_Error_Record 'Proc_S1_GetDataFromUCIS_A00014'

	End Catch
  
End